package com.example.dao.impl;

import com.example.common.R;
import com.example.dao.NewsDao;
import com.example.pojo.entity.WmNews;
import com.example.pojo.vo.LoginVo;
import com.example.pojo.vo.WmNewsVo;
import com.example.utils.JDBCUtil;
import com.example.utils.JWTUtils;
import org.apache.commons.lang3.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class NewsDaoImpl implements NewsDao {

    @Override
    public R getNewsList(String channelId, String title, Integer page, Integer size) throws Exception {
        Connection connection = JDBCUtil.connection();
        String sql1 = null;
        String sql2 = null;
        PreparedStatement ps = null;
        PreparedStatement ps2 = null;
        if (StringUtils.isNoneBlank(channelId)) {
            sql1 = "select * from wm_news inner join ap_user on ap_user.id = wm_news.user_id  " +
                    "where wm_news.channel_id = ? order by wm_news.created_time desc limit ?,? ";
            ps = connection.prepareStatement(sql1);
            ps.setString(1, channelId);
            ps.setInt(2, (page - 1) * size);
            ps.setInt(3, size);

            //查询总记录数
            sql2 = "select count(*) from wm_news inner join ap_user on ap_user.id = wm_news.user_id  " +
                    "where wm_news.channel_id = ? ";
            ps2 = connection.prepareStatement(sql2);
            ps2.setString(1, channelId);

        }
        if (StringUtils.isNoneBlank(title)) {
            sql1 = "select * from wm_news inner join ap_user on ap_user.id = wm_news.user_id  " +
                    "where wm_news.title like ? order by wm_news.created_time desc limit ?,? ";
            ps = connection.prepareStatement(sql1);
            ps.setString(1, "%" + title + "%");
            ps.setInt(2, (page - 1) * size);
            ps.setInt(3, size);

            //查询总记录数
            sql2 =  "select count(*) from wm_news inner join ap_user on ap_user.id = wm_news.user_id  " +
                    "where wm_news.title like ?";
            ps2 = connection.prepareStatement(sql2);
            ps2.setString(1, "%" + title + "%");

        }
        if (StringUtils.isNoneBlank(channelId) && StringUtils.isNoneBlank(title)) {
            sql1 = "SELECT \n" +
                    "    wm_news.*, \n" +
                    "    ap_user.*\n" +
                    "FROM \n" +
                    "    (SELECT * FROM wm_news WHERE wm_news.title LIKE ? AND channel_id = ? LIMIT ?,?) AS wm_news\n" +
                    "INNER JOIN \n" +
                    "    ap_user ON wm_news.user_id = ap_user.id order by wm_news.created_time desc";
            ps = connection.prepareStatement(sql1);
            ps.setString(1, "%" + title + "%");
            ps.setString(2, channelId);
            ps.setInt(3, (page - 1) * size);
            ps.setInt(4, size);

            //查询总记录数
            sql2 = "SELECT \n" +

                    " count(*)\n" +
                    "FROM \n" +
                    "    (SELECT * FROM wm_news WHERE wm_news.title LIKE ? AND channel_id = ? LIMIT ?,?) AS wm_news\n" +
                    "INNER JOIN \n" +
                    "    ap_user ON wm_news.user_id = ap_user.id order by wm_news.created_time desc";
            ps2 = connection.prepareStatement(sql2);
            ps2.setString(1, "%" + title + "%");
            ps2.setString(2, channelId);
        }

        if (StringUtils.isBlank(channelId) && StringUtils.isBlank(title)) {
            sql1 = "select * from wm_news inner join ap_user on ap_user.id = wm_news.user_id limit ?,?";
            ps = connection.prepareStatement(sql1);
            ps.setInt(1, (page - 1) * size);
            ps.setInt(2, size);

            //查询总记录数
            sql2 =  "select count(*) from wm_news inner join ap_user on ap_user.id = wm_news.user_id";
            ps2 = connection.prepareStatement(sql2);
        }

        ResultSet resultSet = ps.executeQuery();

        //查询总记录数
        ResultSet resultSet2 = ps2.executeQuery();


        List<WmNews> wmNewsList = new ArrayList<>();
        while (resultSet.next()) {
            WmNews wmNews = new WmNews();
            //获取数据
            wmNews.setId(resultSet.getInt("id"));
            //用户姓名
            wmNews.setLabels(resultSet.getString("name"));
            wmNews.setTitle(resultSet.getString("title"));
            wmNews.setContent(resultSet.getString("content"));
            wmNews.setImages(resultSet.getString("images"));
            wmNews.setPublishTime(resultSet.getTime("publish_time"));
            wmNewsList.add(wmNews);
        }
        WmNewsVo wmNewsVo = new WmNewsVo();

        //设置分页数据
        if (resultSet2.next()) {
            int totalCount = resultSet2.getInt(1);
            wmNewsVo.setTotal(totalCount);
        }
        wmNewsVo.setWmNewsList(wmNewsList);

        JDBCUtil.closeConn(ps,connection);
        JDBCUtil.closeConn(ps2,connection);
        return R.ok(wmNewsVo);
    }

    /**
     * 获取新闻详情
     *
     * @param newsId
     * @return
     * @throws Exception
     */
    @Override
    public R getNewsDetail(String newsId) throws Exception {
        Connection connection = JDBCUtil.connection();
        String sql1 = "select * from wm_news inner join ap_user on ap_user.id = wm_news.user_id " +
                "inner join wm_channel on wm_channel.id = wm_news.channel_id  where wm_news.id = ?";
        PreparedStatement ps = connection.prepareStatement(sql1);
        ps.setString(1, newsId);
        ResultSet resultSet = ps.executeQuery();
        WmNewsVo wmNews = new WmNewsVo();

        while (resultSet.next()) {
            //获取数据
            wmNews.setId(resultSet.getInt("id"));
            //用户姓名
            wmNews.setLabels(resultSet.getString("name"));
            wmNews.setTitle(resultSet.getString("title"));
            wmNews.setContent(resultSet.getString("content"));
            wmNews.setImages(resultSet.getString("images"));
            wmNews.setCreatedTime(resultSet.getString("created_time"));
            wmNews.setChannelId(resultSet.getInt("channel_id"));
            wmNews.setChannelName(resultSet.getString("channel_name"));
        }
        JDBCUtil.closeConn(ps,connection);
        return R.ok(wmNews);
    }

    /**
     * 获取热点数据
     *
     * @param
     * @return
     * @throws Exception
     */
    @Override
    public R getNewsHotTitle() throws Exception {
        Connection connection = JDBCUtil.connection();
        String sql1 = "SELECT title,id FROM `wm_news` LIMIT 10";
        PreparedStatement ps = connection.prepareStatement(sql1);
        ResultSet resultSet = ps.executeQuery();

        List<WmNews> wmNewsList = new ArrayList<>();
        while (resultSet.next()) {
            WmNews wmNews = new WmNews();
            //获取数据
            wmNews.setId(resultSet.getInt("id"));
            //用户姓名
            wmNews.setTitle(resultSet.getString("title"));
            wmNewsList.add(wmNews);
        }
        JDBCUtil.closeConn(ps,connection);
        return R.ok(wmNewsList);
    }


    /**
     * 发布文章
     *
     * @param wmNews
     */
    @Override
    public R addNews(WmNews wmNews, String token) throws Exception {
        Connection connection = JDBCUtil.connection();
        //解析token
        LoginVo loginVo = JWTUtils.parseToken(token);
        String sql1 = "INSERT INTO wm_news (user_id,title,content,channel_id,labels,created_time,images) values (?,?,?,?,?,?,?)";
        PreparedStatement ps = connection.prepareStatement(sql1);
        ps.setString(1, String.valueOf(loginVo.getId()));
        ps.setString(2, wmNews.getTitle());
        ps.setString(3, wmNews.getContent());
        ps.setInt(4, wmNews.getChannelId());
        ps.setString(5, loginVo.getName());
        ps.setString(6, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
        ps.setString(7, wmNews.getImages());
        for (int i = 1; i < 10001; i++) {
            ps.executeUpdate();
        }
        JDBCUtil.closeConn(ps,connection);
        return R.ok("发布文章成功");
    }

    /**
     * 删除新闻
     *
     * @param newsId
     * @return
     * @throws Exception
     */
    @Override
    public R removeNews(String newsId) throws Exception {
        Connection connection = JDBCUtil.connection();
        String sql1 = "DELETE FROM wm_news WHERE id = ?";
        PreparedStatement ps = connection.prepareStatement(sql1);
        ps.setString(1, newsId);
        ps.executeUpdate();
        JDBCUtil.closeConn(ps,connection);

        return R.ok("删除成功");
    }

    /**
     * 获取热带你文章
     *
     * @return
     * @throws Exception
     */
    @Override
    public R getHotNewsList(Integer size) throws Exception {
        Connection connection = JDBCUtil.connection();
        String sql1 = "select * from wm_news inner join ap_user on ap_user.id = wm_news.user_id " +
                "ORDER BY RAND() limit ?";
        PreparedStatement ps = connection.prepareStatement(sql1);
        ps.setInt(1, size);
        ResultSet resultSet = ps.executeQuery();

        List<WmNews> wmNewsList = new ArrayList<>();
        while (resultSet.next()) {
            WmNews wmNews = new WmNews();
            //获取数据
            wmNews.setId(resultSet.getInt("id"));
            //用户姓名
            wmNews.setLabels(resultSet.getString("name"));
            wmNews.setTitle(resultSet.getString("title"));
            wmNews.setContent(resultSet.getString("content"));
            wmNews.setImages(resultSet.getString("images"));
            wmNews.setPublishTime(resultSet.getTime("publish_time"));
            wmNewsList.add(wmNews);
        }
        JDBCUtil.closeConn(ps,connection);

        return R.ok(wmNewsList);
    }

    /**
     * 获取个人作品
     *
     * @return
     */
    @Override
    public R getPersonWorkList(Integer page, Integer size, String token, String title) throws Exception {
        LoginVo loginVo = JWTUtils.parseToken(token);
        Connection connection = JDBCUtil.connection();
        String sql1 = null;
        PreparedStatement ps = null;
        if (StringUtils.isNoneBlank(title)) {
            sql1 = "select wm_news.id, wm_news.title,wm_news.images,wm_news.labels,wm_news.created_time from wm_news where user_id = ? and title like ?  limit ?,?";
            ps = connection.prepareStatement(sql1);

            ps.setInt(1, loginVo.getId());
            ps.setString(2, title);
            ps.setInt(3, (page - 1) * size);
            ps.setInt(4, size);

        } else {
            sql1 = "select wm_news.id, wm_news.title,wm_news.images,wm_news.labels,wm_news.created_time from wm_news where user_id = ?  limit ?,?";
            ps = connection.prepareStatement(sql1);
            ps.setInt(1, loginVo.getId());
            ps.setInt(2, (page - 1) * size);
            ps.setInt(3, size);
        }


        ResultSet resultSet = ps.executeQuery();

        //查询总记录数
        String sql2 = "SELECT COUNT(*) FROM wm_news where wm_news.user_id = ?";
        PreparedStatement ps2 = connection.prepareStatement(sql2);
        ps2.setInt(1, loginVo.getId());
        ResultSet resultSet2 = ps2.executeQuery();


        List<WmNews> wmNewsList = new ArrayList<>();
        while (resultSet.next()) {
            WmNews wmNews = new WmNews();
            //获取数据
            wmNews.setId(resultSet.getInt("id"));
            //用户姓名
            wmNews.setLabels(loginVo.getName());
            wmNews.setTitle(resultSet.getString("title"));
            wmNews.setImages(resultSet.getString("images"));
            wmNews.setCreatedTime(resultSet.getString("created_time"));
            wmNewsList.add(wmNews);
        }
        WmNewsVo wmNewsVo = new WmNewsVo();
        wmNewsVo.setWmNewsList(wmNewsList);
        //设置分页数据
        if (resultSet2.next()) {
            int totalCount = resultSet2.getInt(1);
            wmNewsVo.setTotal(totalCount);
        }
        JDBCUtil.closeConn(ps2,connection);
        JDBCUtil.closeConn(ps,connection);
        return R.ok(wmNewsVo);
    }

    @Override
    public R updatePersonWork(WmNews wmNews) throws Exception {

        Connection connection = JDBCUtil.connection();
        String sql1 = "UPDATE wm_news SET title = ?, content = ?, channel_id = ?, images = ? WHERE id = ?";
        PreparedStatement ps = connection.prepareStatement(sql1);
        ps.setString(1, wmNews.getTitle());
        ps.setString(2, wmNews.getContent());
        ps.setInt(3, wmNews.getChannelId());
        ps.setString(4, wmNews.getImages());
        ps.setInt(5, wmNews.getId());
        ps.executeUpdate();
       JDBCUtil.closeConn(ps,connection);

        return R.ok("修改成功");
    }

    @Override
    public R deleteWork(String newsId) throws Exception {
        Connection connection = JDBCUtil.connection();
        String sql1 = null;
        PreparedStatement ps = null;
        sql1 = "delete from  wm_news  WHERE id = ?";
        ps = connection.prepareStatement(sql1);
        ps.setString(1, newsId);
        ps.executeUpdate();

        sql1 = "delete from  ap_comment  WHERE news_id = ?";
        ps = connection.prepareStatement(sql1);
        ps.setString(1, newsId);
        ps.executeUpdate();

        JDBCUtil.closeConn(ps,connection);



        return R.ok("删除成功");
    }
}
